package com.jiudao.dao;

import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.google.gson.JsonArray;
import com.jiudao.entity.Branch;
import com.jiudao.entity.JylRiskconPerson;
import com.jiudao.entity.RiskMark;
import com.jiudao.entity.RiskNum;
import com.jiudao.entity.Riskcon;
import com.jiudao.entity.Risklist;

/**
 * 主力险种分析dao
 * @author qiong_c
 *
 */
@Repository
public class MainRiskAnalyseDao extends BaseDao{
	 
	private static int totalAmount;
	/**
	 * 主力险种排名
	 * @param num 组合险件数
	 * @param startTime 起始时间
	 * @param endTime 终止时间
	 * @param intorgName 分公司名
	 * @return
	 */
	public JsonArray getTop10Data(String num, String startTime,String endTime, String branchCode){
		
		String appf = "AND RISKCON."+Riskcon.APPF+"='1' ";
		String start = " AND RISKCON."+Riskcon.APPDATE+" >= '"+startTime+"'";
		String end = " AND RISKCON."+Riskcon.APPDATE+" <= '"+endTime+"'";
		
		String _branch = "";
		if(!StringUtils.isEmpty(branchCode)) {
			_branch = " AND RISKCON."+Riskcon.BRANCH+" = '"+branchCode+"'";
		}
		String riskNum = "";
		if(!StringUtils.isEmpty(num)) {
			riskNum = " AND JYL_RISKCON_PERSON."+JylRiskconPerson.RISK_NUM+" = "+Integer.parseInt(num);
		}
		String riskMark = " AND JYL_RISKCON_PERSON."+JylRiskconPerson.LONG_RISK+" = '长险' ";
		
	String sql = "SELECT "
			    +"count(1) as NUM  "
			    +"," + riskcon.column(Riskcon.CLASSCODE)
			    +","+ risklist.column(Risklist.CLASSNAME)
			+" FROM  "
				+riskcon.tablename()+" as RISKCON  "
			    +"INNER JOIN "+risklist.tablename()+" AS JYL_RISKLIST "
			    +" ON RISKCON."+Riskcon.CLASSCODE+" = JYL_RISKLIST."+Risklist.CLASSCODE
			    +" LEFT JOIN "+jylRiskconPerson.tablename()+" as JYL_RISKCON_PERSON "
			    +" ON RISKCON."+Riskcon.POLICYNO+" = JYL_RISKCON_PERSON."+JylRiskconPerson.POLICYNO
//			    +" INNER JOIN "+branch.tablename()+" AS BRANCH "
//			    +" ON RISKCON."+Riskcon.BRANCH+" = BRANCH."+Branch.BRANCH
			+" WHERE  "
			    +"1 = 1 "
			    +appf
			    +start
			    +end
			    +riskNum
			    +riskMark
			    +_branch
			+" group by  "
			    +""+riskcon.column(Riskcon.CLASSCODE)
			    +","+risklist.column(Risklist.CLASSNAME)
			+" ORDER BY "
				+"NUM "
				+"LIMIT 10";
	
		logger.info(sql);
		String columns="NUM,"+Riskcon.CLASSCODE+","+Risklist.CLASSNAME;
		return executeQuery(sql, columns);
	}
	
	public JsonArray getMainRiskData(String startTime,String endTime, String branchCode) {
		
		String appf = " AND RISKCON."+Riskcon.APPF+"='1' ";
		String start = " AND RISKCON."+Riskcon.APPDATE+" >= '"+startTime+"'";
		String end = " AND RISKCON."+Riskcon.APPDATE+" <= '"+endTime+"'";
		String _branch = "";
		if(!StringUtils.isEmpty(branchCode)) {
			_branch = " AND RISKCON."+Riskcon.BRANCH+" = '"+branchCode+"'";
		}
		String riskMark = " AND JYL_RISKCON_PERSON."+JylRiskconPerson.LONG_RISK+" = '长险' ";
		
		totalAmount = getTotalAmount(startTime,endTime, branchCode);
		String sql ="WITH TTL AS ( "
		   +" SELECT  "
		   +"  count(1) as CNT  "
		   +"  ,sum(RISKCON.TMOUNT) as SUM_TMOUNT "
		  +"   ,JYL_RISKLIST.CLASSNAME "
		+" FROM  "
		 +"  JYLBASE.RISKCON as RISKCON  "
		 +" INNER JOIN JYLFX.JYL_RISKLIST AS JYL_RISKLIST ON RISKCON.CLASSCODE = JYL_RISKLIST.CLASSCODE "
		 +" LEFT JOIN JYLFX.JYL_RISKCON_PERSON AS JYL_RISKCON_PERSON ON RISKCON.POLICYNO = JYL_RISKCON_PERSON.POLICYNO "
//		  +" INNER JOIN JYLFX.BRANCH AS BRANCH ON RISKCON.BRANCH = BRANCH.BRANCH "
		+" WHERE  "
		   +" 1 = 1  "
    		+appf
		    +start
		    +end
		    +riskMark
		    +_branch
	 +" group by  "
		    +" RISKCON.CLASSCODE "
		    +" ,JYL_RISKLIST.CLASSNAME "
		    +" ORDER BY "
		    +"   CNT ) "
	+" SELECT  "
		+" TTL.CNT*1.0/"+totalAmount+" AS  PERCENTAGE "
		+" ,TTL.CNT "
	    +" ,TTL.SUM_TMOUNT/TTL.CNT AS AVERAGE  "
	    +" ,CLASSNAME "
	+" FROM "
		+" TTL "
	+" WHERE"
		+ " TTL.CNT*1.0/"+totalAmount+" >= 0.02";
		
		logger.info(sql);
		String columns="PERCENTAGE,CNT,AVERAGE,"+Risklist.CLASSNAME;
		return executeQuery(sql, columns);
	}
	
	/**
	 * 获取保单总数
	 * @param startTime
	 * @param endTime
	 * @param intorgName
	 * @return
	 */
	public int getTotalAmount(String startTime,String endTime, String branchCode) {
		
		String appf = " AND RISKCON."+Riskcon.APPF+"='1' ";
		String start = " AND RISKCON."+Riskcon.APPDATE+" >= '"+startTime+"'";
		String end = " AND RISKCON."+Riskcon.APPDATE+" <= '"+endTime+"'";
		String _branch = "";
		if(!StringUtils.isEmpty(branchCode)) {
			_branch = " AND RISKCON."+Riskcon.BRANCH+" = '"+branchCode+"'";
		}
		String riskMark = " AND JYL_RISKCON_PERSON."+JylRiskconPerson.LONG_RISK+" = '长险' ";
		
		String sql ="SELECT "
				   +" count(1) as CNT_TOTAL  "
				+"FROM  "
					+riskcon.tablename()+" as RISKCON  "
				    +" INNER JOIN "+risklist.tablename()+" AS JYL_RISKLIST "
				    +" ON RISKCON."+Riskcon.CLASSCODE+" = JYL_RISKLIST."+Risklist.CLASSCODE
				    +" LEFT JOIN "+jylRiskconPerson.tablename()+" AS JYL_RISKCON_PERSON "
				    +" ON RISKCON."+Riskcon.POLICYNO+" = JYL_RISKCON_PERSON."+JylRiskconPerson.POLICYNO
//				    +" INNER JOIN "+branch.tablename()+" AS BRANCH "
//				    +" ON RISKCON."+Riskcon.BRANCH+" = BRANCH."+Branch.BRANCH
			    +" WHERE  "
				   +" 1 = 1 "
				   +appf
				   +start
				   +end
				   +riskMark
				   +_branch;
		logger.info(sql);
		String columns="CNT_TOTAL";
		JsonArray arr = executeQuery(sql, columns);
		return arr.get(0).getAsJsonObject().get("CNT_TOTAL").getAsInt();
	}
	
}
